/**
 * 
 */
package citiesmanagement;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;

import org.h2.jdbc.JdbcSQLException;

import db.DBConnection;
import db.DBInteraction;

/**
 * @author marko
 * This class allows to scan cities given in a file and parse them to memorize in a database
 */
public class CitiesParser {

	// PRIVATE FIELDS
	/**
	 * cities represents the matrix of cities.
	 * The first column indicates the name of the city.
	 * The second column indicates the provence
	 * The third column indicates the CAP
	 */
	private String [][] citiesInfo;
	/**
	 * This field represents the total number of cities
	 */
	private int numberOfCities;
	/**
	 * This static field represents the name of the file which contains all cities
	 */
	private DBConnection dbCon;

	// PRIVATE STATIC FIELDS
	private static String FILE_NAME = "listacomuni.txt"; 
	/**
	 * This static field represents the path of the file
	 */
	private static String FILE_PATH = "./src/files/";
	
	
	public CitiesParser() 
	{
		try {
			dbCon = new DBConnection();
			readNumberOfCities();
			
			// We have to initialize the array
			this.citiesInfo = new String[this.numberOfCities][3];
			
			// We read the info from file
			readFileInformations();
			
			/*
			for(int i=0; i<this.citiesInfo.length; i++)
				System.out.println(citiesInfo[i][0] + ";\t" + citiesInfo[i][1] + ";\t" + citiesInfo[i][2] );
			*/
			
			// Then we save CAPs on DB
			this.insertCAPsInDatabase();
			// Then Province on DB
			this.insertProvencesInDatabase();
			// And last the Cities info
			this.insertCitiesInDatabase();
			

		} catch (SQLException e) {
			// TODO Predisporre messaggio
			e.printStackTrace();
		}
	}
	
	/**
	 * This function allows to read the total number of cities in the file
	 * to initialize the cities array
	 */
	private void readNumberOfCities()
	{
		try 
		{
			this.numberOfCities = -1;
			/**
			 * This code allow to recognize which is the current directory
			File dir = new File("./src/files/");
			System.out.println(dir.getCanonicalPath());
			*/
			BufferedReader reader = new BufferedReader(new FileReader(this.FILE_PATH + this.FILE_NAME));
			String line;
			while((line = reader.readLine()) != null)
			{
				this.numberOfCities++;
			}
			reader.close();
		}
		catch (Exception e) {
			//System.err.format("Eccezione nella lettura del file '%s'. ", this.FILE_PATH + this.FILE_NAME);
			//e.printStackTrace();
			this.numberOfCities = -1;
		}
	}
	
	/**
	 * This function allows to read all the information of the file
	 * and store it in an array of String
	 */
	private void readFileInformations() 
	{
		String [] tempLineContent = new String[9];
		int counter = 0;
		try {
			BufferedReader reader = new BufferedReader(new FileReader(this.FILE_PATH + this.FILE_NAME));
			String line;
			// We have to read the first row which is the legend of file
			line = reader.readLine();
			while((line = reader.readLine()) != null)
			{
				//System.out.println(line);
				tempLineContent = line.split(";");
				// System.out.println(tempLineContent[1]);
				// We first set the name of the city
				this.citiesInfo[counter][0] = tempLineContent[1];
				// Then we set the provence
				this.citiesInfo[counter][1] = tempLineContent[2];
				// Then we set the CAP
				this.citiesInfo[counter][2] = tempLineContent[5];
				counter++;
			}
		}catch (Exception e) {
			e.printStackTrace();
			// In case of exception we set citiesInfo to null
			this.citiesInfo = null;
			// TODO Should we show a popup error?
		}
	}
	
	/**
	 * This function allows to insert all CAPs in the file on database
	 */
	private void insertCAPsInDatabase()
	{
		String query = "INSERT INTO CAP VALUES (?);";
		for(int i=0; i<this.citiesInfo.length; i++)
		{
			try {
				DBInteraction.executeParametricUpdateSingleParameter(this.dbCon, query, this.citiesInfo[i][2]);
			} catch (JdbcSQLException e) {
				// TODO: handle exception
				if(e.getErrorCode() == 23001)
				{
					// Ignore this exception caused by a duplicate cap
				}
			} 
			catch (SQLException e) {
				// TODO Predisporre messaggio errore
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * This function allows to insert all Province in the file on database
	 */
	private void insertProvencesInDatabase()
	{
		String query = "INSERT INTO Provincia VALUES (?);";
		for(int i=0; i<this.citiesInfo.length; i++)
		{
			try {
				DBInteraction.executeParametricUpdateSingleParameter(this.dbCon, query, this.citiesInfo[i][1]);
			} catch (JdbcSQLException e) {
				if(e.getErrorCode() == 23001)
				{
					// Ignore this exception caused by a duplicate cap
				}
			}
			catch (SQLException e) {
				// TODO Predisporre messaggio errore
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * This function allows to insert all cities info in the file on database
	 */
	private void insertCitiesInDatabase()
	{
		String [] temp = new String[3];
		String query = "INSERT INTO Comune(Nome, CapCod, ProvinciaS) " +
				"VALUES (?, ?, ?);";
		for(int i=0; i<this.citiesInfo.length; i++)
		{
			try {
				temp[0] = this.citiesInfo[i][0];
				temp[1] = this.citiesInfo[i][2];
				temp[2] = this.citiesInfo[i][1];
				DBInteraction.executeParametricUpdate(dbCon, query, temp);
			} catch (JdbcSQLException e) {
				if(e.getErrorCode() == 23001)
				{
					// Ignore this exception caused by a duplicate cap
				}
			}
			catch (SQLException e) {
				// TODO Predisporre messaggio errore
				e.printStackTrace();
			}
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		CitiesParser cities = new CitiesParser();
	}

}
